﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class HomeDal
    {
        private DapperHelper dapperHelper { get; set; }





        /// <summary>
        /// 根据负责人分页获取数据
        /// </summary>
        /// <param name="ownerId">负责人ID</param>
        /// <param name="startIndex">开始位置</param>
        /// <param name="endIndex">结束位置</param>
        /// <returns></returns>
        public List<HomeInfo> QueryHomeInfoByPaging(Guid ownerId,int skipRecords ,int pageSize,string order)
        {
            List<HomeInfo> homeInfos = null;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                //string whereSql = string.Format(@"select tmp.*
                //                                from
                //                                (
                //                                 select 
                //                                 r.*,ROW_NUMBER() over(order by r.ScheduledStart desc)Number
                //                                 from (

                //                                  select 
                //                                  t.activityid TaskId,
                //                                  t.Subject,
                //                                  dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                //                                  null as New_mobile,
                //                                  t.OwnerId             --负责人ID
                //                                  from Task t with(nolock)
                //                                  left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                //                                  left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
                //                                  where t.OwnerId='{0}' and t.new_type = 100000000 and t.StateCode = 0 

                //                                  union all

                //                                  select 
                //                                  t.activityid TaskId,
                //                                  t.Subject,
                //                                  dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
                //                                  l.new_phone as New_mobile,
                //                                  t.OwnerId             --负责人ID
                //                                  from Task t with(nolock)
                //                                  left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
                //                                  left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
                //                                  left join LeadBase l with(nolock) on l.LeadId = p.new_lead
                //                                  where t.OwnerId='{0}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
                //                                 )r 
                //                                ) tmp  where tmp.Number between {1} and {2} order by tmp.ScheduledStart {3} ", ownerId, startIndex, endIndex, order);

                string whereSql = string.Format(@"select tmp.*
                                                from
                                                (
	                                                select 
	                                                r.*
	                                                from (

		                                                select 
		                                                t.activityid TaskId,
		                                                t.Subject,
		                                                dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
		                                                null as New_mobile,
		                                                t.OwnerId             --负责人ID
		                                                from Task t with(nolock)
		                                                left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
		                                                left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
		                                                where t.OwnerId='{0}' and t.new_type = 100000000 and t.StateCode = 0 

		                                                union all

		                                                select 
		                                                t.activityid TaskId,
		                                                t.Subject,
		                                                dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
		                                                l.new_phone as New_mobile,
		                                                t.OwnerId             --负责人ID
		                                                from Task t with(nolock)
		                                                left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
		                                                left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
		                                                left join LeadBase l with(nolock) on l.LeadId = p.new_lead
		                                                where t.OwnerId='{0}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
	                                                )r 
                                                ) tmp  order by tmp.ScheduledStart {1} offset {2} rows fetch next {3} rows only", ownerId, order, skipRecords, pageSize);

                homeInfos = a.Query<HomeInfo>(whereSql).ToList();
            });

            return homeInfos;
        }




       /// <summary>
       /// 根据姓名查找
       /// </summary>
       /// <param name="name"></param>
       /// <returns></returns>
        public List<HomeInfo> QueryHomeInfoByName(Guid ownerId,string name)
        {
            List<HomeInfo> homeInfos = null;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select * from AIER_TASK_INFO where OwnerId='{0}' and new_leadName like '%{1}%'", ownerId, name);

                homeInfos = a.Query<HomeInfo>(whereSql).ToList();
            });

            return homeInfos;
        }

        /// <summary>
        /// 根据手机号码查找
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public List<HomeInfo> QueryHomeInfoByMobile(Guid ownerId, string mobile)
        {
            List<HomeInfo> homeInfos = null;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select * from AIER_TASK_INFO where OwnerId='{0}' and new_mobile={1}", ownerId, mobile);

                homeInfos = a.Query<HomeInfo>(whereSql).ToList();
            });

            return homeInfos;
        }

        /// <summary>
        /// 根据座机号码查找
        /// </summary>
        /// <param name="telephone"></param>
        /// <returns></returns>
        public List<HomeInfo> QueryHomeInfoByTelephone(Guid ownerId, string telephone)
        {
            List<HomeInfo> homeInfos = null;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"select * from AIER_TASK_INFO where OwnerId='{0}' and new_tel like '%{1}%'", ownerId, telephone);

                homeInfos = a.Query<HomeInfo>(whereSql).ToList();
            });

            return homeInfos;
        }


        /// <summary>
        /// 总计条数
        /// </summary>
        /// <param name="ownerId"></param>
        /// <returns></returns>
        public int QueryHomeInfoTotal(Guid ownerId)
        {
            int total = 0;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string whereSql = string.Format(@"
	                                        select 
	                                        count(1)
	                                        from 
                                            (
		                                        select 
		                                        t.activityid TaskId,
		                                        t.Subject,
		                                        dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
		                                        null as New_mobile,
		                                        t.OwnerId             --负责人ID
		                                        from Task t with(nolock)
		                                        left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
		                                        left join new_screenBase sc with(nolock) on sc.new_screenId = t.RegardingObjectId
		                                        where t.OwnerId='{0}' and t.new_type = 100000000 and t.StateCode = 0 

		                                        union all

		                                        select 
		                                        t.activityid TaskId,
		                                        t.Subject,
		                                        dbo.fn_UTCToTzCodeSpecificLocalTime(t.ScheduledStart,220)ScheduledStart,
		                                        l.new_phone as New_mobile,
		                                        t.OwnerId             --负责人ID
		                                        from Task t with(nolock)
		                                        left join SystemUserBase s with(nolock) on t.OwnerId = s.SystemUserId
		                                        left join new_propagandaBase p with(nolock) on p.new_propagandaId = t.RegardingObjectId
		                                        left join LeadBase l with(nolock) on l.LeadId = p.new_lead
		                                        where t.OwnerId='{0}' and t.new_type = 100000001 and t.StateCode = 0 and l.StateCode=0
	                                        )r ", ownerId);

                object objValue = a.ExecuteScalar(whereSql);
                if (objValue != null) total = int.Parse(objValue.ToString());
            });

            return total;
        }



    }
}
